//
//  DBTool.m
//  团购
//
//  Created by 樊荣海 on 15/1/22.
//  Copyright (c) 2015年 樊荣海. All rights reserved.
//

#import "DBTool.h"

@implementation DBTool {
    
    FMDatabase *_db;
}

singleton_implementation(DBTool)

- (instancetype)init {
    
    if (self == [super init]) {
        
        //1.创建数据库
        //创建数据库
        NSString *docDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        NSString *dbPath = [docDir stringByAppendingPathComponent:@"database.db"];
        
        NSLog(@"数据库位置: %@", dbPath);
        
        _db = [FMDatabase databaseWithPath:dbPath];
        
        if ([_db open]) {
            
            //创建表 t_City
            NSString *str1 = @"CREATE TABLE IF NOT EXISTS t_City (id integer PRIMARY KEY AUTOINCREMENT,name text)";
            
            //创建表 t_Cate
            NSString *str2 = @"CREATE TABLE IF NOT EXISTS t_Cate (id integer PRIMARY KEY AUTOINCREMENT,superTitle text, subTitle text)";
            
            //创建表 t_Dis
            NSString *str3 = @"CREATE TABLE IF NOT EXISTS t_Dis (id integer PRIMARY KEY AUTOINCREMENT,superTitle text, subTitle text)";
            
            //创建表 t_Order
            NSString *str4 = @"CREATE TABLE IF NOT EXISTS t_Order (id integer PRIMARY KEY AUTOINCREMENT,superTitle text, subTitle text)";
            
            NSString *str5 = @"CREATE TABLE IF NOT EXISTS t_Deal (id integer PRIMARY KEY AUTOINCREMENT,deal_id text,title text,desc text,list_price double,current_price double,purchase_count integer,publish_date text,purchase_deadline text,image_url text,s_image_url text,deal_h5_url text)";
           
            
            [_db executeUpdate:str1];
            [_db executeUpdate:str2];
            [_db executeUpdate:str3];
            [_db executeUpdate:str4];
            [_db executeUpdate:str5];
            
        } else {
            NSLog(@"打开/创建数据库 错误");
        }
        
        
        
    }
    return self;
}

//插入城市
- (void)insertCityModel:(CityModel *)cityModel {
    
    //1.先删除
    NSString *string1 = [NSString stringWithFormat:@"delete from t_City where name='%@'", cityModel.name];
    if ([self executeSQL:string1]) {
        
        NSString *string2 = [NSString stringWithFormat:@"insert into t_City (name) values ('%@')", cityModel.name];
        [self executeSQL:string2];
    }
    
    
}

//查询最近的4个城市
- (NSMutableArray *)selectAllCityModel {
    
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *str = [NSString stringWithFormat:@"SELECT * FROM t_City order by id desc limit 4"];
    
    FMResultSet *result = [_db executeQuery:str];
    
    while ([result next]) {
        
        NSString *name = [result stringForColumn:@"name"];
        
        
        [array addObject:name];
    }
    
    return array;
}

//查询最近的1个城市
- (NSString *)selectCurrentCity {
    
    NSString *cityName = nil;
    
    NSString *str = [NSString stringWithFormat:@"SELECT * FROM t_City order by id desc limit 1"];
    
    FMResultSet *result = [_db executeQuery:str];
    
    while ([result next]) {
        
        cityName = [result stringForColumn:@"name"];
    }
    
    return cityName;
}

//插入分类
- (void)insertSubTitle:(NSString *)cate btn:(DropMenuBtn *)sender {
    
    NSString *noticeName = nil;
    NSString *table = nil;
    if ([sender isKindOfClass:[CateDropBtn class]]) {
        table = @"t_Cate";
        noticeName = kCategoryChangeNote;
    } else if ([sender isKindOfClass:[DisDropBtn class]]) {
        table = @"t_Dis";
        noticeName = kDistrictChangeNote;
    } else if ([sender isKindOfClass:[OrderDropBtn class]]) {
        table = @"t_Order";
        noticeName = kOrderChangeNote;
    }
    
    //1.先删除
    NSString *string1 = [NSString stringWithFormat:@"delete from %@", table];
    if ([self executeSQL:string1]) {
        
        NSString *string2 = [NSString stringWithFormat:@"insert into %@ (superTitle, subTitle) values ('%@', '%@')", table, [sender titleForState:UIControlStateNormal], cate];
        [self executeSQL:string2];
    }
    
    //发出改变topBtn 通知
    [[NSNotificationCenter defaultCenter] postNotificationName:@"initTopTitle" object:nil];
    
    [[NSNotificationCenter defaultCenter] postNotificationName:noticeName object:nil];
    
}

//查询最近的1个分类-superTitle
- (NSString *)selectSuperTitle:(TabelType)tableType {
    NSString *type = nil;
    switch (tableType) {
        case TabelTypeCate:
            type = @"t_Cate";
            break;
        case TabelTypeDis:
            type = @"t_Dis";
            break;
        case TabelTypeOrder:
            type = @"t_Order";
            break;
        default:
            break;
    }
    
    
    NSString *superTitle = nil;
    
    NSString *str = [NSString stringWithFormat:@"SELECT * FROM %@", type];
    
    FMResultSet *result = [_db executeQuery:str];
    
    while ([result next]) {
        
        superTitle = [result stringForColumn:@"superTitle"];
    }
    
    return superTitle;
}

//查询最近的1个分类-subTitle
- (NSString *)selectSubTitle:(TabelType)tableType {
    
    NSString *type = nil;
    switch (tableType) {
        case TabelTypeCate:
            type = @"t_Cate";
            break;
        case TabelTypeDis:
            type = @"t_Dis";
            break;
        case TabelTypeOrder:
            type = @"t_Order";
            break;
        default:
            break;
    }
    
    NSString *title = nil;
    
    NSString *str = [NSString stringWithFormat:@"SELECT * FROM %@", type];
    
    FMResultSet *result = [_db executeQuery:str];
    
    while ([result next]) {
        
        title = [result stringForColumn:@"subTitle"];
    }
    
    return title;
}

- (void)deleteAllSubAndSuperTitle {
    NSString *string1 = [NSString stringWithFormat:@"delete from t_Cate"];
    NSString *string2 = [NSString stringWithFormat:@"delete from t_Dis"];
    NSString *string3 = [NSString stringWithFormat:@"delete from t_Order"];
    
    [self executeSQL:string1];
    [self executeSQL:string2];
    [self executeSQL:string3];
    
    //发出改变topBtn 通知
    [[NSNotificationCenter defaultCenter] postNotificationName:@"initTopTitle" object:nil];
}


//根据deal_ID查询dealModel是否存在于数据库
- (BOOL)dealIsExist:(DealModel *)dealModel {
    
    
    //查询
    BOOL exist = NO;
    
    NSString *str = [NSString stringWithFormat:@"SELECT * FROM t_Deal where deal_id = '%@'", dealModel.deal_id];
    
    FMResultSet *result = [_db executeQuery:str];
    
    while ([result next]) {
        
        exist = YES;
    }
    
    return exist;
    
}

//插入一个dealModel
- (void)insertDealModel:(DealModel *)dealModel {
    
    NSString *string2 = [NSString stringWithFormat:@"insert into t_Deal (deal_id,title,desc,list_price, current_price,purchase_count,publish_date,purchase_deadline,image_url,s_image_url,deal_h5_url) values ('%@','%@','%@','%f','%f','%d','%@','%@','%@','%@','%@')", dealModel.deal_id, dealModel.title , dealModel.desc, dealModel.list_price, dealModel.current_price, dealModel.purchase_count, dealModel.publish_date, dealModel.purchase_deadline, dealModel.image_url, dealModel.s_image_url, dealModel.deal_h5_url];
    
    [self executeSQL:string2];

}


//删掉一个dealModel
- (void)deleteDealModel:(DealModel *)dealModel {

    NSString *string = [NSString stringWithFormat:@"delete from t_Deal where deal_id = '%@'", dealModel.deal_id];
    
    [self executeSQL:string];
}



//执行
- (BOOL)executeSQL:(NSString *)sql {
    
    if ([_db executeUpdate:sql]) {
        NSLog(@"执行成功:%@", sql);
        
        return YES;
    } else {
        NSLog(@"执行失败:%@", sql);
        
        return NO;
    }
}


@end
